package com.booza.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.classic.Session;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.booza.constants.DatabaseConstants;
import com.booza.constants.PlayerInvitationStatus;
import com.booza.constants.TournamentStatus;
import com.booza.model.Tournament;
import com.booza.model.TournamentDef;
import com.booza.model.TournamentInstance;
import com.booza.model.TournamentPlayers;
import com.booza.util.ConverterUtil;

/**
 * A hibernate DAO class for tournament and tournamentplayers table. 
 * No business logic here. Just the interaction with the database.
 * 
 * @see TournamentDAO
 */
@Transactional
public class TournamentDAOImpl extends CommonDAOImpl implements TournamentDAO
{
	@Autowired
	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory()
	{
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory)
	{
		this.sessionFactory = sessionFactory;
	}

	public void createTournament(Tournament tr)
	{
		sessionFactory.getCurrentSession().save(tr);
	}

	// Finds the winner for this tournament.
	private static final String TOURNAMENT_WINNER_QUERY = "SELECT b.userid FROM account b, "
			+ "(SELECT tournamentid, (CASE WHEN score1 > score2 THEN player1 ELSE player2 END) AS player, round FROM stats "
			+ "WHERE (tournamentid, round) IN (SELECT tournamentid, MAX(round) FROM stats WHERE tournamentid = :id and tournamenttype=:ttype GROUP BY tournamentid) AND tournamentid = :id AND tournamenttype = :ttype) a "
			+ "WHERE a.player = b.accountid  GROUP BY b.accountid, a.player;";

	// Finds round-by-round information for the tournament.
	private static final String TOURNAMENT_DETAILS_QUERY = "SELECT tournamentid AS tournamentid, "
			+ "(CASE WHEN score1 > score2 THEN (SELECT userid FROM account WHERE accountid = player1) ELSE (SELECT userid FROM account WHERE accountid = player2) END) AS winner,"
			+ "(CASE WHEN score1 > score2 THEN score1 ELSE score2 END) AS winscore, ROUND,"
			+ "(CASE WHEN score1 < score2 THEN (SELECT userid from account where accountid = player1) ELSE (SELECT userid FROM account WHERE accountid = player2) END) AS loser,"
			+ "(CASE WHEN score1 < score2 THEN score1 ELSE score2 END) AS loseScore FROM stats WHERE tournamentid = :id AND tournamenttype=:ttype ORDER BY round";

	// Finds winner for all tournaments.
	private static final String ALL_TOURNAMENTS_WINNER_QUERY = "SELECT a.tid, b.userid FROM account b, "
			+ "(SELECT tournamentid as tid,(CASE WHEN score1 > score2 THEN player1 ELSE player2 END) AS player, round FROM stats"
			+ " WHERE (tournamentid, round) IN (SELECT tournamentid, MAX(round) FROM stats WHERE tournamentid IN :id and tournamenttype=:ttype "
			+ "GROUP BY tournamentid) AND tournamentid IN :id AND tournamenttype = :ttype) a "
			+ "WHERE a.player = b.accountid  GROUP BY b.accountid,a.player;";

	/**
	 * Obtains all tournaments so far.
	 * 
	 * @return Return a list of all of the tournaments.
	 */
	@SuppressWarnings("unchecked")
	public Collection<Tournament> fetchAll()
	{
		return (Collection<Tournament>) sessionFactory.getCurrentSession().createCriteria(Tournament.class).list();
	}

	/**
	 * Find tournaments matching the criteria.
	 * 
	 * @param colName
	 *          - colname of the criteria
	 * @param colValue
	 *          - keyword
	 * @return Return the matching tournaments.
	 */
	@SuppressWarnings("unchecked")
	public Collection<Tournament> fetchAllByParam(String colName, Object colValue)
	{
		return (Collection<Tournament>) sessionFactory.getCurrentSession().createCriteria(Tournament.class).add(
				Restrictions.eq(colName, colValue)).list();
	}

	/**
	 * Adds player to the tournament.
	 * 
	 * @param tp
	 *          -player to be added
	 */
	public void addPlayerToTournament(TournamentPlayers tp)
	{
		sessionFactory.getCurrentSession().save(tp);
	}

	/**
	 * Find players for the given tournament.
	 * 
	 * @param id
	 *          - id of the tournament
	 * @return list of players participating in the tournament
	 */
	@SuppressWarnings("unchecked")
	public Collection<TournamentPlayers> findPlayersForTournament(Integer id)
	{
		return (Collection<TournamentPlayers>) sessionFactory.getCurrentSession().createCriteria(TournamentPlayers.class)
				.add(Restrictions.eq("tournamentid", id)).list();
	}

	/**
	 * Executes a generic query in tournament table.
	 * 
	 * @param query
	 *          -query to be executed
	 * @param colName
	 *          - column in the criteria
	 * @param val
	 *          - value to be applied
	 * @return results matching the criteria
	 */
	@SuppressWarnings("unchecked")
	public Collection<Tournament> executeQuery(String query, String colName, Object val)
	{
		return (List<Tournament>) sessionFactory.getCurrentSession().createQuery(query).setParameter(colName, val).list();
	}

	/**
	 * Executes a generic query in <code>tournamentplayers</code> table.
	 * 
	 * @param query
	 *          -query to be executed
	 * @param colName
	 *          - column in the criteria
	 * @param val
	 *          - value to be applied
	 * @return results matching the criteria
	 */
	@SuppressWarnings("unchecked")
	public Collection<TournamentPlayers> executeQueryForPlayers(String query, String colName, Object val)
	{
		return (List<TournamentPlayers>) sessionFactory.getCurrentSession().createQuery(query).setParameter(colName, val)
				.list();
	}

	public Tournament findTournamentById(Integer id)
	{
		return (Tournament) sessionFactory.getCurrentSession().get(Tournament.class, id);
	}

	/**
	 * Checks if the player has been added already for the given tournament.
	 * 
	 * @param accountId
	 *          - player ID
	 * @param tournamentId
	 *          - tournament ID
	 * @return player details if already added,null if not
	 */
	public TournamentPlayers findPlayersById(Integer accountId, Integer tournamentId)
	{
		return (TournamentPlayers) sessionFactory.getCurrentSession().createCriteria(TournamentPlayers.class).add(
				Restrictions.eq("accountid", accountId)).add(Restrictions.eq("tournamentid", tournamentId)).uniqueResult();
	}

	@SuppressWarnings("unchecked")
	public Collection<Integer> getTournamentsByTime(Date startTime)
	{
		Collection<Tournament> t = (Collection<Tournament>) sessionFactory.getCurrentSession().createCriteria(
				Tournament.class).add(Restrictions.eq("starttime", startTime)).add(Restrictions.eq("status", 0)).list();
		if (t != null) {
			List<Integer> ids = new ArrayList<Integer>();
			for (Tournament tt : t) {
				ids.add(tt.getTournamentid());
			}
			return ids;
		}
		return null;
	}

	/**
	 * Find list of players invited for the tournament.
	 * 
	 * @param friendId
	 * @param tournamentId
	 *          - id of tournament
	 * @return list of players
	 */
	@SuppressWarnings("unchecked")
	public Collection<TournamentPlayers> getInvitedPlayers(Integer friendId, Integer tournamentId)
	{
		return (Collection<TournamentPlayers>) sessionFactory.getCurrentSession().createCriteria(TournamentPlayers.class)
				.add(Restrictions.eq("accountid", friendId)).add(Restrictions.eq("tournamentid", tournamentId)).list();
	}

	/**
	 * Update the tournament player details.
	 * 
	 * @param player
	 *          - details to be updated
	 */
	public void updateTournamentPlayer(TournamentPlayers player)
	{
		sessionFactory.getCurrentSession().saveOrUpdate(player);
	}

	@SuppressWarnings("unchecked")
	public Collection<TournamentPlayers> getAccepted(Integer accountId, Date startTime)
	{
		return (Collection<TournamentPlayers>) sessionFactory.getCurrentSession().createCriteria(TournamentPlayers.class)
				.add(Restrictions.eq("accountid", accountId)).add(Restrictions.eq("accepted", 1)).uniqueResult();
	}

	@SuppressWarnings("unchecked")
	public Collection<TournamentPlayers> executeQuery(String query, Object obj)
	{
		return (List<TournamentPlayers>) sessionFactory.getCurrentSession().createQuery(query).setProperties(obj).list();
	}

	/**
	 * Find players who has accepted a tournament an invitation which starts at the given time.
	 * 
	 * @param startTime
	 *          - time at which the tournament starts
	 * @param accountId
	 *          -
	 * @return players
	 */
	@SuppressWarnings("unchecked")
	public Collection<TournamentPlayers> getConflictingTournaments(Date startTime, Integer accountId)
	{
		Collection<Integer> tournamentIds = getTournamentsByTime(startTime);
		if (tournamentIds == null || tournamentIds.isEmpty()) {
			return null;
		}
		return (Collection<TournamentPlayers>) sessionFactory.getCurrentSession().createCriteria(TournamentPlayers.class)
				.add(Restrictions.eq("accountid", accountId)).add(Restrictions.eq("accepted", 1)).add(
						Restrictions.in("tournamentid", tournamentIds)).list();
	}

	/**
	 * Find list of system generated tournaments.
	 * 
	 * @return Return list of system generated tournaments.
	 */
	@SuppressWarnings("unchecked")
	public List<TournamentDef> getSystemTournaments()
	{		
		return (List<TournamentDef>) sessionFactory.getCurrentSession().createCriteria(TournamentDef.class).list();		
	}

	/**
	 * Find list of tournaments which starts tomorrow, for which players have accepted.
	 * 
	 * @return
	 */
	public List getTournamentsEligibleForReminder()
	{
		return sessionFactory
				.getCurrentSession()
				.createSQLQuery(
				"SELECT b.userid, b.email, c.name, DATE_FORMAT(d.playertime,'%H:%i:%s'), d.playerrefid "
								+ "FROM account b, tournamentplayers a, tournament c, tournamentreminders d WHERE a.tournamentid IN "
								+ "(SELECT p.tournamentid FROM tournamentplayers p, tournamentreminders r WHERE (to_days(r.playertime)-to_days(current_date) <= 1) "
								+ "AND p.tournamentplayersid = r.playerrefid AND p.accepted = 1 AND r.reminded = 0) AND a.accountid = b.accountid AND c.tournamentid = a.tournamentid "
								+ "AND b.invitations = 1 AND a.tournamentplayersid = d.playerrefid")
				.list();
	}

	/**
	 * Find tournaments created by the user and whose status is finished.
	 * 
	 * @param accountId
	 *          - id of the user
	 * @return Return matching tournament details.
	 */
	public Collection<Tournament> findPreviousTournaments(Integer accountId)
	{
		String query = "FROM Tournament WHERE (((creator = :id) OR (tournamentid IN (SELECT p.tournamentid from TournamentPlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED + "))) AND (status = " + TournamentStatus.FINISHED + ")) ORDER BY starttime desc";
		return executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find tournaments for which invitations has been sent.
	 * 
	 * @param accountId
	 *          - id of the user
	 * @return Return matching tournament details.
	 */
	public Collection<Tournament> findTournamentsInvitations(Integer accountId)
	{
		String query = "FROM Tournament WHERE (tournamentid IN (SELECT p.tournamentid FROM TournamentPlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.NOT_RESPONDED
				+ ")) AND (starttime > current_date OR starttime IS NULL) AND (status <> " + TournamentStatus.FINISHED + ")";
		return executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find tournaments to be held in future time. Finds tournaments created by the user.
	 * 
	 * @param accountId
	 *          - id of the user
	 * @return Return matching tournament details.
	 */
	public Collection<Tournament> findUpcomingTournaments(Integer accountId)
	{
		String query = "FROM Tournament WHERE (((creator = :id) OR (tournamentid IN (SELECT p.tournamentid FROM TournamentPlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED
				+ "))) AND (starttime > current_date OR starttime IS NULL) AND (status = "
				+ TournamentStatus.NOT_STARTED + ")" + " ) ORDER BY starttime";
		return executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find tournaments for which invitations can be sent.
	 * 
	 * @param accountId
	 *          - id of the user
	 * @return Return matching tournament details.
	 */
	public Collection<Tournament> findUpcomingTournamentsCanInvite(Integer accountId)
	{
		String query = "FROM Tournament WHERE (((creator = :id) OR (tournamentid IN (SELECT p.tournamentid FROM TournamentPlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED
				+ ") AND friendsinvite = 1)) AND (starttime > current_date OR starttime IS NULL) AND (status <> "
				+ TournamentStatus.FINISHED + "))";
		return executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find the winner of a specific tournament.
	 * 
	 * @param tournamentId
	 * @return
	 */
	public String findWinnerOfTournament(Integer tournamentId, String tournamentType)
	{
		return (String) sessionFactory.getCurrentSession().createSQLQuery(
				TOURNAMENT_WINNER_QUERY.replace(":id", tournamentId + "").replace(":ttype", tournamentType)).uniqueResult();
	}

	/**
	 * Find details about the given tournament. Retrieves and winner/loser information and their respective scores of all rounds.
	 * 
	 * @param tournamentId
	 * @return
	 */
	public List getTournamentDetails(Integer tournamentId, String tournamentType)
	{
		return (List) sessionFactory.getCurrentSession().createSQLQuery(
				TOURNAMENT_DETAILS_QUERY.replace(":id", tournamentId + "").replace(":ttype", tournamentType)).list();
	}

	/**
	 * Find tournament by tournament name.
	 * 
	 * @param name
	 * @return
	 */
	public Tournament findTournamentByName(String name)
	{
		return (Tournament) sessionFactory.getCurrentSession().createCriteria(Tournament.class).add(
				Restrictions.eq("name", name)).uniqueResult();
	}

	/**
	 * Finds winner names for all tournaments of this type.
	 * 
	 * @param tournamentId
	 * @param tournamentType
	 * @return
	 */
	public List findWinnerOfAllTournaments(List<Integer> tournamentId, String tournamentType)
	{
		return (List) sessionFactory.getCurrentSession().createSQLQuery(
				ALL_TOURNAMENTS_WINNER_QUERY.replace(":id", ConverterUtil.listToSQLString(tournamentId)).replace(":ttype",
						tournamentType)).list();
	}

	/**
	 * Find a system tournament by primary key.
	 * 
	 * @param id
	 * @return
	 */
	public TournamentDef findSystemTournament(Integer id)
	{
		return (TournamentDef) sessionFactory.getCurrentSession().createCriteria(TournamentDef.class).add(
				Restrictions.eq("tournamentid", id)).uniqueResult();
	}

	/**
	 * Find a running tournament involving this user.
	 * 
	 * @param accountId
	 * @return
	 */
	public Collection<Tournament> findRunningTournaments(Integer accountId)
	{
		String query = "FROM Tournament WHERE (((creator = :id) OR (tournamentid IN (SELECT p.tournamentid FROM TournamentPlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED + "))) AND (status = " + TournamentStatus.RUNNING + ")) ORDER BY starttime";
		return executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find system tournaments which are yet to commence.
	 */
	@SuppressWarnings("unchecked")
	public Collection<TournamentDef> findFreshSysTournaments()
	{
		String query = "FROM TournamentDef WHERE tournamentid NOT IN "
				+ "(SELECT t.tournamentid FROM tournamentdef t, tournamentinstance instance WHERE t.tournamentid = instance.tournamentid AND instance.status > 0)";
		return (List<TournamentDef>) sessionFactory.getCurrentSession().createQuery(query).list();
	}

	/**
	 * Delete the tournament and the players list for this tournament.
	 */
	@SuppressWarnings("unchecked")
	public boolean deleteTournament(Tournament tournament)
	{
		Session session = sessionFactory.getCurrentSession();
		Set<TournamentPlayers> players = tournament.getPlayers();
		for (TournamentPlayers p : players) {
			session.delete(p);
		}
		session.delete(tournament);
		return true;
	}

	/**
	 * Find all draft tournament by the creator.
	 */
	public Collection<Tournament> findDraftTournaments(Integer accountId)
	{
		String query = "FROM Tournament WHERE (((creator = :id) "
				+ ") AND (starttime > current_date OR starttime IS NULL) AND (status = " + TournamentStatus.DRAFT + ")"
				+ ") ORDER BY starttime";
		return executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Change the tournament status. Only owner can change the status.
	 */
	public boolean changeTournamentStatus(Integer accountId, Integer tornamentId, int status)
	{
		boolean changed = false;
		Tournament tournament = (Tournament) sessionFactory.getCurrentSession().get(Tournament.class, tornamentId);
		if (tournament != null) {
			// only owner can delete the tournament
			if (tournament.getCreator().equals(accountId) == true) {
				tournament.setStatus(status);
				sessionFactory.getCurrentSession().saveOrUpdate(tournament);
				changed = true;
			}
		}
		return changed;
	}

	public void updateTournament(Integer accountId, Tournament tournament)
	{
		sessionFactory.getCurrentSession().saveOrUpdate(tournament);
	}

	/**
	 * Find all system generated tournament instances.
	 */
	@SuppressWarnings("unchecked")
	public List<TournamentInstance> getSystemGeneratedTournamentIntances()
	{
		String query = "FROM TournamentInstance";
		return (List<TournamentInstance>) sessionFactory.getCurrentSession().createQuery(query).list();
	}
	
	/**
	 * Updates reminded column as 'reminded(1)' for the list of playerrefids supplied
	 */
	public int markRemindedEmailsAsSent(String listofIds){
		String hql = "UPDATE TournamentReminders SET reminded = 1 WHERE playerrefid IN (" + listofIds + ")";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		int rowCount = query.executeUpdate();
		return rowCount;
	}
}
